﻿USE [ITPro]
GO

/****** Object:  Table [dbo].[TaiKhoan]    Script Date: 05/11/2012 13:22:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
/*** Tao bang Tai Khoan */

create TABLE [dbo].[TaiKhoan](
	[TaiKhoan] [nvarchar](50) NOT NULL,
	[MatKhau] [nvarchar](255) NULL,
	[HoTen] [nvarchar](500) NULL,
	[TrangThai] [bit] NULL,
	[MaNV] [nvarchar](7) NULL,
 CONSTRAINT [PK_TaiKhoan] PRIMARY KEY CLUSTERED 
(
	[TaiKhoan] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/*** Het Tao bang Tai Khoan ***/

/*** Tao bang Phan Quyen ***/

create TABLE [dbo].[DanhMucPhanQuyen](
	[MaQuyen] [nvarchar](5) NOT NULL,
	[TenQuyen] [nvarchar](50) NULL,		
)

GO
create TABLE [dbo].[PhanQuyen](
	ID INT IDENTITY(1,1) NOT NULL,
	[Quyen] [nvarchar](5) NOT NULL,
	[TaiKhoan] [nvarchar](50) NULL,		
)

GO

ALTER TABLE [dbo].[PhanQuyen]  WITH CHECK ADD  CONSTRAINT [FK_PhanQuyen_TaiKhoan] FOREIGN KEY([TaiKhoan])
REFERENCES [dbo].[TaiKhoan] ([TaiKhoan])
GO

ALTER TABLE [dbo].[PhanQuyen] CHECK CONSTRAINT [FK_PhanQuyen_TaiKhoan]
GO

/*** Het Tao bang Phan Quyen ***/
/* tao bang Phòng ban */
create table NV_PhongBan
(
MaPhong INT IDENTITY(1,1) NOT NULL,
TenPhong nvarchar(255)
primary key(MaPhong)
)
go
/* tao bang NhanVien */
create table NV_NhanVien
(
MaNV nvarchar(7),
TenNV nvarchar(255),
DienThoaiNB nvarchar(15),
DienThoaiDD nvarchar(15),
Mail nvarchar(100),
PHG int,
TrangThai bit,
TaiKhoan nvarchar(255),
MatKhau nvarchar(255)
primary key(MaNV)
)
go

/* tao bang Chinh sach he thong */
create table NV_ChinhSachHeThong
(
MaNV nvarchar(7),
GuiMail bit,
USB bit,
Internet bit,
Yahoo bit,
Skype bit,
)
go

/* tao bang Code dien thoai */
create table NV_CodeDienThoai
(
IDCode INT IDENTITY(1,1) NOT NULL,
MaNV nvarchar(7),
NgayCap datetime,
NgayHuy datetime,
LoaiCode int,
CapLan1 nvarchar(15),
CapLan2 nvarchar(15),
CapLan3 nvarchar(15),
CapLan4 nvarchar(15),
CapLan5 nvarchar(15),
primary key (IDCode)
)
go
/* tao bang Loai Code */
create table NV_LoaiCode
(
MaLoai INT IDENTITY(1,1) NOT NULL,
MoTa nvarchar(255)
primary key (MaLoai)
)
go
/* tao bang Loai Code 
create table NV_ChiTietCode
(
IDChiTiet INT IDENTITY(1,1) NOT NULL,
CapLan1 nvarchar(15),
CapLan2 nvarchar(15),
CapLan3 nvarchar(15),
CapLan4 nvarchar(15),
CapLan5 nvarchar(15),
IDCode int
primary key (IDChiTiet)
)
go
*/
/* tao bang Tinh trang May */
Create table MT_TinhTrang
(
IDTT nvarchar(2),
TenTT nvarchar(255),
primary key (IDTT)
)
go
/* tao bang Nha Cung Cap */
Create table MT_NhaCungCap
(
MaNhaCC nvarchar(5),
TenNhaCC nvarchar(255),
DienThoai nvarchar(255),
DiaChi nvarchar(255)
primary key (MaNhaCC)
)
go
/* tao bang Danh Muc Linh Kien */
Create table MT_DanhMucLinhKien
(
MaLK nvarchar(7),
TenLK nvarchar(255),
primary key (MaLK)
)
go

/* tao bang Danh Muc May Vi Tinh */
Create table MT_MayViTinh
(
MaMay nvarchar(5),
NgayMua datetime,
NgayHetHan datetime,
NhaCungCap nvarchar(5),
TinhTrang nvarchar(2)
primary key (MaMay)
)
go
/* tao bang Chi tiet May Vi Tinh */
Create table MT_ChiTietMayTinh
(
IDChiTiet INT IDENTITY(1,1) NOT NULL,
MaMay nvarchar(5),
LinhKien nvarchar(7),
HanBaoHanh datetime
primary key (IDChiTiet)
)
go
/* tao bang CV Ban Giao May */
create table CV_BanGiaoThietBi
(
IDBG INT IDENTITY(1,1) NOT NULL,
MaMay nvarchar(5),
NgayGiao datetime,
NguoiGiao nvarchar(7),
NguoiNhan nvarchar(7),
TinhTrang  nvarchar(50),
Giu bit,
primary key (IDBG)
)
go
/* tao bang CV Ban Giao Link Kien */
create table CV_BanGiaoLinhKien
(
IDBGLK INT IDENTITY(1,1) NOT NULL,
MaLK nvarchar(7),
MaMay nvarchar(5),
NgayGiao datetime,
NguoiGiao nvarchar(7),
NguoiNhan nvarchar(7),
TinhTrang  nvarchar(2),
primary key (IDBGLK)
)


/********** Insert Du lieu ************/
insert into MT_DanhMucLinhKien values ('MECS478','Mainboard ECS Socket 478')
insert into MT_DanhMucLinhKien values ('MECS775','Mainboard ECS Socket 775')
insert into MT_DanhMucLinhKien values ('MASU478','Mainboard ASUS Socket 478')
insert into MT_DanhMucLinhKien values ('MASU775','Mainboard ASUS Socket 775')
insert into MT_DanhMucLinhKien values ('MGIG478','Mainboard Gigabyte Socket 478')
insert into MT_DanhMucLinhKien values ('MGIG775','Mainboard Gigabyte Socket 775')
insert into MT_DanhMucLinhKien values ('MBIO478','Mainboard BioStar Socket 478')
insert into MT_DanhMucLinhKien values ('MBIO775','Mainboard BioStar Socket 775')
insert into MT_DanhMucLinhKien values ('MASR478','Mainboard Asrock Socket 478')
insert into MT_DanhMucLinhKien values ('MASR775','Mainboard Asrock Socket 775')
insert into MT_DanhMucLinhKien values ('R512DD1','512 MB DDR 1')
insert into MT_DanhMucLinhKien values ('R512DD2','512 MB DDR 2')
insert into MT_DanhMucLinhKien values ('R1GDDR1','1 GB DDR 1')
insert into MT_DanhMucLinhKien values ('R2GDDR1','2 GB DDR 1')
insert into MT_DanhMucLinhKien values ('R1GDDR2','1 GB DDR 2')
insert into MT_DanhMucLinhKien values ('R2GDDR2','2 GB DDR 2')
insert into MT_DanhMucLinhKien values ('R1GDDR3','1 GB DDR 3')
insert into MT_DanhMucLinhKien values ('R2GDDR3','2 GB DDR 3')
insert into MT_DanhMucLinhKien values ('R4GDDR3','4 GB DDR 3')
insert into MT_DanhMucLinhKien values ('HDSS40G','HDD 40 GB Sam Sung')
insert into MT_DanhMucLinhKien values ('HDSS80G','HDD 80 GB Sam Sung')
insert into MT_DanhMucLinhKien values ('HDSS160','HDD 160 GB Sam Sung')
insert into MT_DanhMucLinhKien values ('HDSS250','HDD 250 GB Sam Sung')
insert into MT_DanhMucLinhKien values ('HDSS320','HDD 320 GB Sam Sung')
insert into MT_DanhMucLinhKien values ('HDSS500','HDD 500 GB Sam Sung')
insert into MT_DanhMucLinhKien values ('HDSS1TB','HDD 1 TB Sam Sung')
insert into MT_DanhMucLinhKien values ('HDSE40G','HDD 40 GB SeaGate')
insert into MT_DanhMucLinhKien values ('HDSE80G','HDD 80 GB SeaGate')
insert into MT_DanhMucLinhKien values ('HDSE160','HDD 160 GB SeaGate')
insert into MT_DanhMucLinhKien values ('HDSE250','HDD 250 GB SeaGate')
insert into MT_DanhMucLinhKien values ('HDSE320','HDD 320 GB SeaGate')
insert into MT_DanhMucLinhKien values ('HDSE500','HDD 500 GB SeaGate')
insert into MT_DanhMucLinhKien values ('HDSE1TB','HDD 1 TB SeaGate')
insert into MT_DanhMucLinhKien values ('HDMA40G','HDD 40 GB Maxtor')
insert into MT_DanhMucLinhKien values ('HDMA80G','HDD 80 GB Maxtor')
insert into MT_DanhMucLinhKien values ('HDMA160','HDD 160 GB Maxtor')
insert into MT_DanhMucLinhKien values ('MO15SEC','Monitor 15inch Second-hand')
insert into MT_DanhMucLinhKien values ('MO17SEC','Monitor 17inch Second-hand')
insert into MT_DanhMucLinhKien values ('MO19SEC','Monitor 19inch Second-hand')
insert into MT_DanhMucLinhKien values ('CPU18P4','CPU 1.8GHz Socket 478')
insert into MT_DanhMucLinhKien values ('CPU20P4','CPU 2.0GHz Socket 478')
insert into MT_DanhMucLinhKien values ('CP226P4','CPU 2.26GHz Socket 478')
insert into MT_DanhMucLinhKien values ('CP266P4','CPU 2.66GHz Socket 478')
insert into MT_DanhMucLinhKien values ('CPU28P4','CPU 2.6GHz Socket 478')
insert into MT_DanhMucLinhKien values ('CPU30P4','CPU 3.0GHz Socket 478')
insert into MT_DanhMucLinhKien values ('CPU1877','CPU 1.8GHz Socket 775')
insert into MT_DanhMucLinhKien values ('CP22677','CPU 2.26GHz Socket 775')
insert into MT_DanhMucLinhKien values ('CP26677','CPU 2.66GHz Socket 775')
insert into MT_DanhMucLinhKien values ('CPU2877','CPU 2.8GHz Socket 775')
insert into MT_DanhMucLinhKien values ('CPU3077','CPU 3.0GHz Socket 775')
insert into MT_DanhMucLinhKien values ('KEYMITS','Keyboard Mitsumi')
insert into MT_DanhMucLinhKien values ('KEYBIBM','Keyboard IBM')
insert into MT_DanhMucLinhKien values ('MOUSIBM','Mouse IBM')
insert into MT_DanhMucLinhKien values ('MOUMITS','Mouse Mitsumi')
insert into MT_DanhMucLinhKien values ('MOUDELL','Mouse Dell')

-- het insert Danh Muc Linh Kien
select * from MT_DanhMucLinhKien order by MaLK Asc

select * from MT_TinhTrang
insert into MT_TinhTrang values('01',N'Bình thường')
insert into MT_TinhTrang values('02',N'Hỏng')
insert into MT_TinhTrang values('03',N'Thanh lý - nhập kho')
select * from MT_NhaCungCap
select * from TaiKhoan

select * from MT_MayViTinh
select * from MT_ChiTietMayTinh

--delete from MT_MayViTinh
--delete from MT_ChiTietMayTinh
select * from MT_DanhMucLinhKien
delete from MT_DanhMucLinhKien where MaLK='MO17SEC'
select * from TaiKhoan
select ct.IDChiTiet,dmlk.TenLK,ct.HanBaoHanh from MT_MayViTinh mt,MT_ChiTietMayTinh ct,MT_DanhMucLinhKien dmlk where mt.MaMay=ct.MaMay	and ct.LinhKien=dmlk.MaLK and mt.MaMay='ddd'

select * from NV_PhongBan
select * from NV_NhanVien nv,NV_PhongBan pb where nv.PHG=pb.MaPhong

select * from NV_CodeDienThoai
insert NV_LoaiCode(MoTa) values (N'Di động')
insert NV_LoaiCode(MoTa) values (N'Di động + Liên Tỉnh')

select * from NV_ChinhSachHeThong

select * from NV_NhanVien nv,
				NV_ChinhSachHeThong csht,
				NV_PhongBan pb,
				NV_CodeDienThoai code,
				NV_LoaiCode loaicode,
				CV_BanGiaoThietBi bgtb,
				MT_MayViTinh mt,
				MT_NhaCungCap ncc,
				MT_TinhTrang tt,
				MT_ChiTietMayTinh ctmt,
				MT_DanhMucLinhKien lk
		where nv.PHG=pb.MaPhong 
			and nv.MaNV=csht.MaNV 
			and	nv.MaNV=code.MaNV 
			and code.LoaiCode=loaicode.MaLoai 
			and	nv.MaNV=bgtb.NguoiNhan 
			and	bgtb.MaMay=mt.MaMay 
			and	mt.NhaCungCap=ncc.MaNhaCC 
			and	mt.TinhTrang=tt.IDTT 
			and	mt.MaMay=ctmt.MaMay 
			and ctmt.LinhKien=lk.MaLK 
			and nv.MaNV='LBC013'
			
			
			
----
select * from NV_NhanVien nv,
				NV_ChinhSachHeThong csht,
				NV_PhongBan pb,
				NV_CodeDienThoai code,
				NV_LoaiCode loaicode				
		where nv.PHG=pb.MaPhong 
			and nv.MaNV=csht.MaNV 
			and	nv.MaNV=code.MaNV 
			and code.LoaiCode=loaicode.MaLoai 			
			and nv.MaNV='LBC020'
	
select * from NV_NhanVien nv,NV_PhongBan pb where nv.PHG=pb.MaPhong and nv.MaNV='LBC013'

select * from CV_BanGiaoThietBi
select *  from MT_MayViTinh where MT_MayViTinh.MaMay not in(select CV_BanGiaoLinhKien.MaMay from CV_BanGiaoLinhKien)
select * from NV_LoaiCode

select mt.MaMay+'-'+ ncc.TenNhaCC +'-'+ convert(varchar,mt.NgayMua,101)+'-'+ convert(varchar,mt.NgayHetHan,101)as MayTinh,dmlk.TenLK, ct.HanBaoHanh,ct.NhaCungCap as LKNhaCC, tt.TenTT  
from MT_MayViTinh mt,MT_ChiTietMayTinh ct,MT_NhaCungCap ncc,MT_TinhTrang tt,MT_DanhMucLinhKien dmlk 
where mt.MaMay=ct.MaMay and mt.NhaCungCap=ncc.MaNhaCC and mt.TinhTrang=tt.IDTT and ct.LinhKien=dmlk.MaLK

select *
from MT_MayViTinh mt,MT_ChiTietMayTinh ct,MT_NhaCungCap ncc,MT_TinhTrang tt,MT_DanhMucLinhKien dmlk,MT_ChiTietMayTinh Left Join MT_NhaCungCap on MT_ChiTietMayTinh.NhaCungCap=MT_NhaCungCap.MaNhaCC
where mt.MaMay=ct.MaMay and mt.NhaCungCap=ncc.MaNhaCC

select *
from MT_NhaCungCap Left Join MT_ChiTietMayTinh  on MT_ChiTietMayTinh.NhaCungCap=MT_NhaCungCap.MaNhaCC left join MT_MayViTinh on MT_ChiTietMayTinh.MaMay=MT_MayViTinh.MaMay


select *
from MT_MayViTinh left join MT_NhaCungCap on MT_MayViTinh.NhaCungCap=MT_NhaCungCap.MaNhaCC
				left join MT_ChiTietMayTinh on MT_MayViTinh.MaMay=MT_ChiTietMayTinh.MaMay
				left join MT_DanhMucLinhKien on MT_ChiTietMayTinh.LinhKien=MT_DanhMucLinhKien.MaLK
				left join MT_TinhTrang on MT_MayViTinh.TinhTrang=MT_TinhTrang.IDTT
				left join (select * from MT_NhaCungCap,MT_ChiTietMayTinh where MT_ChiTietMayTinh.NhaCungCap=MT_NhaCungCap.MaNhaCC)
				

-- danh sach nhan vien voi chinh sách he thong va code dien thoai
select * 
from NV_NhanVien left join  NV_ChinhSachHeThong on NV_NhanVien.MaNV=NV_ChinhSachHeThong.MaNV 
				 left join NV_CodeDienThoai on NV_NhanVien.MaNV=NV_CodeDienThoai.MaNV 
				 where NV_NhanVien.PHG='3'
				 
				 left join CV_BanGiaoThietBi on NV_NhanVien.MaNV=CV_BanGiaoThietBi.NguoiNhan and CV_BanGiaoThietBi.Giu='1'
				 
	
select *			 
	from NV_NhanVien left join  CV_BanGiaoThietBi on NV_NhanVien.MaNV=CV_BanGiaoThietBi.NguoiNhan and CV_BanGiaoThietBi.Giu='1'
		where NV_NhanVien.PHG='6'		 
		
		
create table MT_DanhMucLinhKienPhongIT
(
ID INT IDENTITY(1,1) NOT NULL,
MaLinhKien nvarchar(7),
HanBaoHanh Datetime,
NhaCC nvarchar(5),
TinhTrang nvarchar(2)
primary key (ID)
)

select * from MT_DanhMucLinhKienPhongIT
Select 0 as STT,* from MT_DanhMucLinhKienPhongIT lkit,MT_DanhMucLinhKien dm, MT_TinhTrang tt  where lkit.MaLinhKien=dm.MaLK and lkit.TinhTrang=tt.IDTT

select * from CV_BanGiaoLinhKien
select * from CV_ChiTietBanGiaoLinhKien

<!--
create table Temp_BanGiaThietBi
(
ID INT IDENTITY(1,1) NOT NULL,
NguoiNhan nvarchar(50),
NguoiGiao nvarchar(50),
MayTinh nvarchar(5),
NgayGiao datetime,
TinhTrang nvarchar(50),
GhiChu nvarchar(255),
TenLinhKien nvarchar(255),
primary key(ID)
)
go
-->

select * from Temp_BanGiaThietBi
select * from CV_BanGiaoThietBi bg,MT_ChiTietMayTinh ctmt,MT_DanhMucLinhKien dm where ctmt.LinhKien=dm.MaLK and bg.MaMay=ctmt.MaMay and bg.Giu=1 and bg.NguoiNhan='LBC402' and bg.MaMay='PC052'




-------// phan he khoi tao website
create proc sp_HienThiDanhSachPhongBan
as
begin	
	select * from NV_PhongBan
end


alter proc sp_LoadDanhSachNhanVienThuocPhong
@phg int
as
begin
	if(@phg=0)
	select * from NV_NhanVien nv,NV_PhongBan pb where  nv.PHG=pb.MaPhong and (len(nv.DienThoaiNB)>0 or len(nv.DienThoaiDD)>0) and nv.TrangThai='1' order by pb.TenPhong ASC
	else	
	select * from NV_NhanVien nv,NV_PhongBan pb where nv.PHG=@phg 
			and (len(nv.DienThoaiNB)>0 or len(nv.DienThoaiDD)>0)			
			and nv.PHG=pb.MaPhong	
			and nv.TrangThai='1'		
end

exec sp_HienThiDanhSachPhongBan
exec sp_LoadDanhSachNhanVienThuocPhong 0

alter proc sp_test
@phg int
as
begin
	if(@phg=0)
	select * from NV_NhanVien nv,NV_PhongBan pb where  nv.PHG=pb.MaPhong and (len(nv.DienThoaiNB)>0 or len(nv.DienThoaiDD)>0) and nv.TrangThai='1' order by pb.TenPhong ASC
	else	
	select * from NV_NhanVien nv,NV_PhongBan pb where nv.PHG=@phg 
			and (len(nv.DienThoaiNB)>0 or len(nv.DienThoaiDD)>0)			
			and nv.PHG=pb.MaPhong	
			and nv.TrangThai='1'		
end
exec sp_test 0

--- tao bang cv ghi nhan thuc hien yeu cau
create table CV_GhiNhanYeuCauIT
(
	ID INT IDENTITY(1,1) NOT NULL,
	NgayYeuCau datetime,
	NgayHoanThanh datetime,
	NguoiYeuCau nvarchar(7),
	NguoiThucHien nvarchar(7),
	NguoiDuyet nvarchar(7),
	LoaiYeuCau int,
	NoiDung nvarchar(max),
	Suly int,
	ApDung int,
	NgayKetThuc datetime,
	ThucHien int,
	GhiChu nvarchar(500),
	primary key(ID)
)
go
create table CV_LoaiYeuCau
(
	IDLYC INT IDENTITY(1,1) NOT NULL,
	TenLoai nvarchar(50),
	primary key(IDLYC)
)
go
create table CV_DanhMucSuLy
(
	IDSL INT IDENTITY(1,1) NOT NULL,
	TenSuLy nvarchar(50),
	primary key(IDSL)
)
go
create table CV_LoaiApDung
(
	IDAD INT IDENTITY(1,1) NOT NULL,
	TenLoaiAD nvarchar(50),
	primary key(IDAD)
)
go


insert into CV_LoaiYeuCau(TenLoai) values(N'1. Yêu cầu Thay đổi loại hình cuộc gọi')
insert into CV_LoaiYeuCau(TenLoai) values(N'2. Yêu cầu xem, lưu dữ liệu backup')
insert into CV_LoaiYeuCau(TenLoai) values(N'3. Yêu cầu cấp quyền truy cập server')
insert into CV_LoaiYeuCau(TenLoai) values(N'4. Đăng ký sử dụng chính sách hệ thống')
insert into CV_LoaiYeuCau(TenLoai) values(N'5.Khác')

insert into CV_DanhMucSuLy(TenSuLy) values(N'1. Chờ sử lý')
insert into CV_DanhMucSuLy(TenSuLy) values(N'2. Đang sử lý')
insert into CV_DanhMucSuLy(TenSuLy) values(N'3. Chờ mua hàng')
insert into CV_DanhMucSuLy(TenSuLy) values(N'4. Sử lý xong')

insert into CV_LoaiApDung(TenLoaiAD) values(N'1. Không thời hạn')
insert into CV_LoaiApDung(TenLoaiAD) values(N'2. Hàng Tháng')
insert into CV_LoaiApDung(TenLoaiAD) values(N'3. Theo Thời gian')


--Select 0 as STT,* from MT_DanhMucLinhKienPhongIT lkit,MT_DanhMucLinhKien dm, MT_TinhTrang tt  where lkit.MaLinhKien=dm.MaLK and lkit.TinhTrang=tt.IDTT

create proc cv_LoadDanhSachSuLy
as
begin
select * from CV_DanhMucSuLy
end

alter proc cv_LoadLoaiYeuCau
as
begin
select '0' as IDLYC, N'[Chọn Loại Yêu cầu]' as TenLoai  
UNION
select * from CV_LoaiYeuCau
end

create proc cv_LoadLoaiApDung
as
begin
select * from CV_LoaiApDung
end

exec cv_LoadDanhSachSuLy

--------------------------**** phan he ghi nhan yeu cau procedure


create proc cv_ThemGhiNhanYeuCauIT
@ngayyc Datetime,
@nguoiyc nvarchar(7),
@nguoith nvarchar(7),
@nguoiduyet nvarchar(7),
@loaiyc int,
@noidung nvarchar(max),
@apdung int,
@ngaykt datetime,
@ghichu nvarchar(500)
as
begin
	if(@apdung=3)
	begin
		insert into CV_GhiNhanYeuCauIT(NgayYeuCau,NguoiYeuCau,NguoiThucHien,NguoiDuyet,LoaiYeuCau,NoiDung,Suly,ApDung,NgayKetThuc,ThucHien, GhiChu) 
								values(@ngayyc,@nguoiyc,@nguoith,@nguoiduyet,@loaiyc,@noidung,'1',@apdung,@ngaykt,'0',@ghichu)
	end
	else
	begin
		insert into CV_GhiNhanYeuCauIT(NgayYeuCau,NguoiYeuCau,NguoiThucHien,NguoiDuyet,LoaiYeuCau,NoiDung,Suly,ApDung,GhiChu) 
								values(@ngayyc,@nguoiyc,@nguoith,@nguoiduyet,@loaiyc,@noidung,'1',@apdung,@ghichu)
	end

end

alter proc cv_CapNhatGhiNhanYeuCauIT
@id int,
@suly int,
@nguoith nvarchar(7),
@ngayhoanthanh datetime
as
begin
	update CV_GhiNhanYeuCauIT set Suly=@suly,NguoiThucHien=@nguoith,NgayHoanThanh=@ngayhoanthanh where ID=@id
end

alter proc cv_KetThucHanYeuCauIT
@id int,
@thuchien int,
@ngayhoanthanh datetime
as
begin
update CV_GhiNhanYeuCauIT set ThucHien=@thuchien,Suly='4',NgayHoanThanh=@ngayhoanthanh
 where ID=@id
end

alter proc cv_HienThiDanhSachYC
@suly int,
@ngbd datetime,
@ngaykt datetime
as
begin
	select *,(select TenNV from NV_NhanVien where MaNV=gn.NguoiYeuCau) as TenNguoiYC,(select TenNV from NV_NhanVien where MaNV=gn.NguoiThucHien) as TenNguoiTH,(select TenNV from NV_NhanVien where MaNV=gn.NguoiDuyet) as TenNguoiDuyet from  CV_GhiNhanYeuCauIT gn,CV_LoaiApDung ad,CV_LoaiYeuCau yc 
	where gn.Suly=@suly and gn.ApDung=IDAD and gn.LoaiYeuCau=yc.IDLYC
			and gn.NgayYeuCau between @ngbd and @ngaykt
end
--------------------------**** het phan he ghi nhan yeu cau

alter proc cv_LichHenKetThucYC
@thuchien int
as
begin
	select *,(select TenNV from NV_NhanVien where MaNV=gn.NguoiYeuCau) as TenNguoiYC,(select TenNV from NV_NhanVien where MaNV=gn.NguoiThucHien) as TenNguoiTH,(select TenNV from NV_NhanVien where MaNV=gn.NguoiDuyet) as TenNguoiDuyet  from CV_GhiNhanYeuCauIT gn,CV_LoaiYeuCau yc 
		 where ApDung =3 and ThucHien =@thuchien and gn.LoaiYeuCau=yc.IDLYC
end

--delete from CV_GhiNhanYeuCauIT
--DBCC CHECKIDENT ('dbo.CV_GhiNhanSuaChua', RESEED, 1)  // reset IDENTITY  ==================================================================

exec cv_HienThiDanhSachYC '2','07-01-2012','07-10-2012' 

select * from CV_GhiNhanYeuCauIT

exec cv_LichHenKetThucYC '0'

-- phan he ghi nhan sua chua IT

create table CV_GhiNhanSuaChua
(
	ID INT IDENTITY(1,1) NOT NULL,
	NgayYeuCau datetime,	
	NguoiYeuCau nvarchar(7),
	NguoiThucHien nvarchar(7),
	NgayThucHien datetime,
	LoaiSuaChua int,
	MaMay nvarchar(5),
	NoiDung nvarchar(max),
	Suly int,
	NguyenNhan nvarchar(500),
	KhacPhuc nvarchar(500),
	GhiChu nvarchar(500),
	NgayHoanThanh datetime,	
	MayTinh nvarchar(255),
	primary key(ID)
)
go

create table CV_LoaiYCSuaChua
(
	IDSC INT IDENTITY(1,1) NOT NULL,
	TenLoaiSC nvarchar(50),
	primary key(IDSC)
)

create table CV_LoaiLoi
(
	IDLoi int,
	TenLoaiLoi nvarchar(500)
	
)

-- insert loai YC sua chua
insert into CV_LoaiYCSuaChua(TenLoaiSC) values(N'1. Máy tính')
insert into CV_LoaiYCSuaChua(TenLoaiSC) values(N'2. Máy In')
insert into CV_LoaiYCSuaChua(TenLoaiSC) values(N'3. Mạng Lan')
insert into CV_LoaiYCSuaChua(TenLoaiSC) values(N'4. Server')
insert into CV_LoaiYCSuaChua(TenLoaiSC) values(N'5. Điện thoại')
insert into CV_LoaiYCSuaChua(TenLoaiSC) values(N'6. Máy Photocopy - Fax')
insert into CV_LoaiYCSuaChua(TenLoaiSC) values(N'7. Khác')



insert into CV_LoaiLoi values('1',N'1.01 Cháy RAM')
insert into CV_LoaiLoi values('1',N'1.02 Cháy HDD')
insert into CV_LoaiLoi values('1',N'1.03 Cháy nguồn')
insert into CV_LoaiLoi values('1',N'1.04 Cháy card mạng')
insert into CV_LoaiLoi values('1',N'1.05 Cháy màn hình')
insert into CV_LoaiLoi values('1',N'1.06 Cháy CPU')
insert into CV_LoaiLoi values('1',N'1.07 Lỏng dây VGA')
insert into CV_LoaiLoi values('1',N'1.08 chưa cắm dây chuột')
insert into CV_LoaiLoi values('1',N'1.09 chưa cắm dây bàn phím')
insert into CV_LoaiLoi values('1',N'1.10 chưa cắm dây mạng')
insert into CV_LoaiLoi values('1',N'1.11 chưa cắm dây nguồn')
insert into CV_LoaiLoi values('1',N'1.12 Lỏng card mạng')
insert into CV_LoaiLoi values('1',N'1.13 Lỗi RAM')
insert into CV_LoaiLoi values('1',N'1.14 Treo card mạng')
insert into CV_LoaiLoi values('1',N'1.15 lỗi HDD')
insert into CV_LoaiLoi values('1',N'1.16 lỗi Hệ điều hành')
insert into CV_LoaiLoi values('1',N'1.17 Full ổ cứng')
insert into CV_LoaiLoi values('1',N'1.18 lỗi driver')
insert into CV_LoaiLoi values('1',N'1.19 Nhiễm Virus')
insert into CV_LoaiLoi values('1',N'1.20 Khác')

insert into CV_LoaiLoi values('2',N'2.1 Kẹt giấy')
insert into CV_LoaiLoi values('2',N'2.2 Hư bao lụa')
insert into CV_LoaiLoi values('2',N'2.3 hư thanh sấy')
insert into CV_LoaiLoi values('2',N'2.4 Hư dây tín hiệu')
insert into CV_LoaiLoi values('2',N'2.5 Khác')

insert into CV_LoaiLoi values('3',N'3.1 Đứt dây cáp link')
insert into CV_LoaiLoi values('3',N'3.2 Hỏng switch')
insert into CV_LoaiLoi values('3',N'3.3 Khác')

insert into CV_LoaiLoi values('4',N'4.1 Server Quá Tải')
insert into CV_LoaiLoi values('4',N'4.2 Khác')

insert into CV_LoaiLoi values('5',N'5.1 Đứt dây')
insert into CV_LoaiLoi values('5',N'5.2 gãy đầu RJ11')
insert into CV_LoaiLoi values('5',N'5.3 Điện thoại hỏng')
insert into CV_LoaiLoi values('5',N'5.4 Khác')

insert into CV_LoaiLoi values('6',N'6.1 kẹt giấy')
insert into CV_LoaiLoi values('6',N'6.2 Không có tín hiệu')
insert into CV_LoaiLoi values('6',N'6.3 Khác')

insert into CV_LoaiLoi values('7',N'7.1 Khác')

select * from CV_LoaiLoi

create proc cv_LoaiYeuCauSuaChua
as
begin
	select * from CV_LoaiYCSuaChua
end

alter proc cv_LapPhieuYeuCauSuaChua
@ngayyc datetime,
@nguoiyc nvarchar(7),
@loaiyc int,
@noidung nvarchar(max),
@mamay nvarchar(5),
@maytinh nvarchar(255)
as
begin
	insert into CV_GhiNhanSuaChua(NgayYeuCau,NguoiYeuCau,LoaiSuaChua,NoiDung,Suly,MaMay,MayTinh) values(@ngayyc,@nguoiyc,@loaiyc,@noidung,'1',@mamay,@maytinh)
end

create proc cv_HienThiDanhSachYCSuaChua
@suly int,
@ngbd datetime,
@ngaykt datetime
as
begin
	select *,(select TenNV from NV_NhanVien where MaNV=gn.NguoiYeuCau) as TenNguoiYC,(select TenNV from NV_NhanVien where MaNV=gn.NguoiThucHien) as TenNguoiTH 
	from  CV_GhiNhanSuaChua gn,CV_LoaiYCSuaChua loaiyc,CV_DanhMucSuLy dmsl
	where gn.Suly=@suly
			and gn.LoaiSuaChua=loaiyc.IDSC
			and gn.SuLy=dmsl.IDSL			
			and gn.NgayYeuCau between @ngbd and @ngaykt
end

exec cv_HienThiDanhSachYCSuaChua '1','6-1-2012','7-11-2012'

select * from CV_GhiNhanSuaChua

alter proc cv_HienThiDanhSachLoi
@loi int
as
begin
select * from CV_LoaiLoi where IDLoi=@loi order by TenLoaiLoi asc
end

exec cv_HienThiDanhSachLoi 2

create proc cv_CapNhatTinhTrangYCSC
@id int,
@nguoith nvarchar(7),
@ngayth datetime
as
begin
	update CV_GhiNhanSuaChua set NguoiThucHien=@nguoith,NgayThucHien=@ngayth,Suly='2' where ID=@id
end

alter proc cv_CapNhatXuLyYeuCauSC
@id int,
@xuly int,
@nguoith nvarchar(7),
@nguyennhan nvarchar(500),
@khacphuc nvarchar(500),
@ghichu nvarchar(500),
@ngayht datetime
as
begin	
	if(@ngayht='1-1-2001' )
	begin
	update CV_GhiNhanSuaChua set Suly=@xuly,NguoiThucHien=@nguoith,NguyenNhan=@nguyennhan,KhacPhuc=@khacphuc,GhiChu=@ghichu where ID=@id
	end
	else
	begin
	update CV_GhiNhanSuaChua set Suly=@xuly,NguoiThucHien=@nguoith,NguyenNhan=@nguyennhan,KhacPhuc=@khacphuc,GhiChu=@ghichu,NgayHoanThanh=@ngayht where ID=@id
	end
end

create proc cv_HuyPhieuYeuCauSC
@id int
as
begin
	delete CV_GhiNhanSuaChua where ID=@id 
end


select * from CV_GhiNhanSuaChua


select 0 as STT,* from CV_BanGiaoThietBi bg,NV_NhanVien nv where bg.NguoiGiao=nv.MaNV and bg.NguoiNhan='LBC402' and Giu='1'

alter proc kt_KiemTraLichThucHien 
@ngaylamviec datetime
as
begin
	select * from CV_GhiNhanYeuCauIT where ThucHien=0 and NgayKetThuc<@ngaylamviec
end

exec kt_KiemTraLichThucHien '07-26-2012'

alter proc kt_KiemTraPhieuYCSCMoi
as
begin
	select * from CV_GhiNhanSuaChua gn,CV_LoaiYCSuaChua loaiyc where  gn.LoaiSuaChua=loaiyc.IDSC and NguoiThucHien is NULL
end

exec kt_KiemTraPhieuYCSCMoi





--- phan he tong dai dien thoai

create table CV_KichHoatTongDai
(
	ID INT IDENTITY(1,1) NOT NULL,
	NgayTH datetime,
	NguoiYC nvarchar(7),
	NguoiTH nvarchar(7),
	LoaiYC nvarchar(3)
	primary key(ID)
)

create proc ThemYCKichHoatTongDai
@ngayth datetime,
@nguoiyc nvarchar(7),
@nguoith nvarchar(7),
@loaiyc nvarchar(3)
as
begin
	insert into CV_KichHoatTongDai(NgayTH,NguoiYC,NguoiTH,LoaiYC) values (@ngayth,@nguoiyc,@nguoith,@loaiyc )
end


--------------------***********************--- Phan he website request Nhan vien

create proc website_login
@taikhoan nvarchar(255),
@matkhau nvarchar(255)
as
begin
	select * from NV_NhanVien where TaiKhoan=@taikhoan and MatKhau=@matkhau
end

create proc website_info_email
@taikhoan nvarchar(255)
as
begin
select * from NV_NhanVien where TaiKhoan=@taikhoan
end

alter proc mt_kiemtramaytinh
@mamay nvarchar(5)
as
begin
	
select * from MT_MayViTinh where MaMay=@mamay
end


-----------------------******************************

-- doi mat khau

create proc tk_DoiMatKhau
@tk nvarchar(50),
@mkcu nvarchar(50),
@mkmoi nvarchar(50)
as
begin
	if(not exists(select * from NV_NhanVien where TaiKhoan=@tk and MatKhau=@mkcu))
		begin
		raiserror(' Mật khẩu cũ chưa đúng, vui lòng nhập lại',16,1)
		return 0
		end
	else
	begin
	update NV_NhanVien set MatKhau=@mkmoi
			where TaiKhoan=@tk
			return 1
			end
end
go

---------------------
create proc cv_DoiThongTinMayTinh
@id int,
@mamay nvarchar(5)
as
begin
	update CV_GhiNhanSuaChua set MaMay=@mamay where ID=@id
end

------------- reporting ban giao thiet bi
create table R_BanGiaoThietBi
(
STT int,
NgayGiao datetime,
NguoiGiao nvarchar(255),
NguoiGiaoPhong nvarchar(255),
NguoiNhan nvarchar(255),
NguoiNhanPhong nvarchar(255),
MaMay nvarchar(5),
TenLinhKien nvarchar(255),
TinhTrang nvarchar(50),
GhiChu nvarchar(500)
)

select * from R_BanGiaoThietBi

alter proc sp_R_BanGiaoThietBi
@mamay nvarchar(50)
as
begin
select 0 as STT,bg.NgayGiao,(select TenNV from NV_NhanVien where bg.NguoiGiao=NV_NhanVien.MaNV) as NguoiGiao,(select TenPhong from NV_NhanVien,NV_PhongBan where bg.NguoiGiao=NV_NhanVien.MaNV and NV_NhanVien.PHG=NV_PhongBan.MaPhong) as NguoiGiaoPhong,(select TenNV from NV_NhanVien where bg.NguoiNhan=NV_NhanVien.MaNV) as NguoiNhan,(select TenPhong from NV_NhanVien,NV_PhongBan where bg.NguoiNhan=NV_NhanVien.MaNV and NV_NhanVien.PHG=NV_PhongBan.MaPhong) as NguoiNhanPhong,bg.MaMay,dm.TenLK as TenLinhKien,bg.TinhTrang,bg.GhiChu,bg.IDBG as ID from CV_BanGiaoThietBi bg,MT_ChiTietMayTinh ctmt,MT_DanhMucLinhKien dm
	where bg.Giu=1
			and bg.MaMay=@mamay
			and bg.MaMay=ctmt.MaMay
			and ctmt.LinhKien=dm.MaLK
end

select 0 as STT,bg.NgayGiao,(select TenNV from NV_NhanVien where bg.NguoiGiao=NV_NhanVien.MaNV) as NguoiGiao,(select TenPhong from NV_NhanVien,NV_PhongBan where bg.NguoiGiao=NV_NhanVien.MaNV and NV_NhanVien.PHG=NV_PhongBan.MaPhong) as NguoiGiaoPhong,(select TenNV from NV_NhanVien where bg.NguoiNhan=NV_NhanVien.MaNV) as NguoiNhan,(select TenPhong from NV_NhanVien,NV_PhongBan where bg.NguoiNhan=NV_NhanVien.MaNV and NV_NhanVien.PHG=NV_PhongBan.MaPhong) as NguoiNhanPhong,bg.MaMay,dm.TenLK as TenLinhKien,bg.TinhTrang,bg.GhiChu from CV_BanGiaoThietBi bg,MT_ChiTietMayTinh ctmt,MT_DanhMucLinhKien dm
	where bg.Giu=1
			and bg.MaMay='PC003'
			and bg.MaMay=ctmt.MaMay
			and ctmt.LinhKien=dm.MaLK
			
---- het phan bao cao

exec sp_R_BanGiaoThietBi 'PC007'